<%
'On Error goto 0
On Error resume next
Response.Addheader "Content-Type","text/html; charset=gb2312" 

set conn = server.CreateObject("ADODB.Connection") '创建连接变量
set rs_bz = server.CreateObject("ADODB.Recordset") '创建连接变量
set rs = server.CreateObject("ADODB.Recordset") '创建连接变量
conn.Open "DRIVER={SQL Server};Server=(local);UID=sa;pwd=123456;DATABASE=HPTsoft" '通过dsn语句打开连接


rs.Open "DROP TABLE AAnewTotalLst",conn '删除数据表
'rs.Open "create AAnewTotalLst;",conn '创建数据表,非必要

sqlstr = "SELECT *, CAST(0 AS DECIMAL(10, 2)) AS 早餐补贴金额 ,"
sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 午餐补贴金额 ,"
sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 晚餐补贴金额 ,"
sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 夜宵补贴金额 ,"
sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 补贴总金额 "
sqlstr = sqlstr & " INTO AAnewTotalLst FROM TotalLst;"
rs.Open sqlstr,conn '复制TotalLst表数据到AAnewTotalLst表


sqlstr = "SELECT Name,早餐补贴金额,午餐补贴金额,晚餐补贴金额,夜宵补贴金额 FROM AA部门补贴标准"
rs_bz.Open sqlstr,conn '查询部门补贴标准
do while not rs_bz.eof
	sqlstr = "UPDATE AAnewTotalLst SET "
    sqlstr = sqlstr & "早餐补贴金额 = 早餐消费次数 * " & rs_bz("早餐补贴金额") & " ,"
    sqlstr = sqlstr & "午餐补贴金额 = 午餐消费次数 * " & rs_bz("午餐补贴金额") & " ,"
    sqlstr = sqlstr & "晚餐补贴金额 = 晚餐消费次数 * " & rs_bz("晚餐补贴金额") & " ,"
    sqlstr = sqlstr & "夜宵补贴金额 = 夜宵消费次数 * " & rs_bz("夜宵补贴金额") & " "
    sqlstr = sqlstr & "where 部门名称 = '" & rs_bz("Name") & "'"
    rs.Open sqlstr,conn '更新AAnewTotalLst表补贴金额
	rs_bz.movenext
loop

sqlstr = "UPDATE AAnewTotalLst SET "
sqlstr = sqlstr & "早餐补贴金额 = (SELECT SUM(早餐补贴金额) FROM AAnewTotalLst WHERE [部门名称] <> '合计：'),"
sqlstr = sqlstr & "午餐补贴金额 = (SELECT SUM(午餐补贴金额) FROM AAnewTotalLst WHERE [部门名称] <> '合计：'),"
sqlstr = sqlstr & "晚餐补贴金额 = (SELECT SUM(晚餐补贴金额) FROM AAnewTotalLst WHERE [部门名称] <> '合计：'),"
sqlstr = sqlstr & "夜宵补贴金额 = (SELECT SUM(夜宵补贴金额) FROM AAnewTotalLst WHERE [部门名称] <> '合计：')"
sqlstr = sqlstr & "WHERE [部门名称] = '合计：'"
rs.Open sqlstr,conn '更新AAnewTotalLst表补贴合计金额

sqlstr = "UPDATE AAnewTotalLst SET 补贴总金额 = 早餐补贴金额 + 午餐补贴金额 + 晚餐补贴金额 + 夜宵补贴金额"
rs.Open sqlstr,conn '更新AAnewTotalLst表合计补贴总金额

sqlstr = "SELECT 部门名称,早餐消费次数,早餐补贴金额,午餐消费次数,午餐补贴金额,晚餐消费次数,晚餐补贴金额,夜宵消费次数,夜宵补贴金额,消费总金额,补贴总金额 FROM AAnewTotalLst"
rs.Open sqlstr,conn '查询最终数据表


response.write "{""data"":["
do while not rs.eof
	response.write "{"
	response.write """部门名称""" & ":""" & rs("部门名称") & ""","
	response.write """早餐消费次数""" & ":""" & rs("早餐消费次数") & ""","
	response.write """早餐补贴金额""" & ":""" & rs("早餐补贴金额") & ""","
	response.write """午餐消费次数""" & ":""" & rs("午餐消费次数") & ""","
	response.write """午餐补贴金额""" & ":""" & rs("午餐补贴金额") & ""","
	response.write """晚餐消费次数""" & ":""" & rs("晚餐消费次数") & ""","
	response.write """晚餐补贴金额""" & ":""" & rs("晚餐补贴金额") & ""","
	response.write """夜宵消费次数""" & ":""" & rs("夜宵消费次数") & ""","
	response.write """夜宵补贴金额""" & ":""" & rs("夜宵补贴金额") & ""","
	response.write """消费总金额""" & ":""" & rs("消费总金额") & ""","
	response.write """补贴总金额""" & ":""" & rs("补贴总金额") & """}"
	rs.movenext
    If not rs.eof Then
        response.write ","
    End If
loop
response.write "],"
response.write """err"":{""code"":" & Err.Number & ",""Source"":""" & Err.Source & """,""Description"":""" & Err.Description & """}"
response.write "}"
%>